<html>
<!-- =====================================================================

  File:      usp_CustomerAlsoBought.htm for Adventure Works Cycles Storefront Sample
  Summary:   Self-documentation for application
  Date:	     June 16, 2003

=====================================================================

  This file is part of the Microsoft SQL Server Code Samples.
  Copyright (C) Microsoft Corporation.  All rights reserved.

This source code is intended only as a supplement to Microsoft
Development Tools and/or on-line documentation.  See these other
materials for detailed information regarding Microsoft code samples.

THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE.

======================================================= -->
    <head>
        <title>Adventure Works Cycles Store Documentation</title>
        <link rel="stylesheet" href="style.css">
    </head>
    <body>
        <h1>"usp_CustomerAlsoBought" Stored Procedure</h1>

        <b>Description:</b>

        <P>This is the most interesting and involved of the Customer stored procedures. It
        accepts a ProductID as an input. First, in the inner nested SELECT statement,
        it builds a set of all orders containing the ProductID. Then of those records,
        it returns the top 5 distinct products in those orders. This stored procedure
        give the Adventure Works Cycles application the "Customers who bought this item also bought"
        functionality.</P>

        <b>Definition:</b>

<pre style="background-color:white">
<span style="color: #0000FF"><strong>CREATE</strong></span> PROCEDURE usp_CustomerAlsoBought
<span style="color: #4444FF">(</span>
    @ProductID <span style="color: #2040a0"><strong>int</strong></span>
<span style="color: #4444FF">)</span>
<span style="color: #0000FF"><strong>AS</strong></span>

<span style="color: #008000">/* We want to take the top 5 products contained in
    the orders where someone has purchased the given Product */</span>
<span style="color: #0000FF"><strong>SELECT</strong></span>  TOP 5 
    SOD.ProductID<span style="color: #4444FF">,</span>
    P.[<span style="color: #0000FF"><strong>Name</strong></span>]<span style="color: #4444FF">,</span>
    SUM<span style="color: #4444FF">(</span>SOD.OrderQty<span style="color: #4444FF">)</span> <span style="color: #0000FF"><strong>as</strong></span> TotalNum

<span style="color: #0000FF"><strong>FROM</strong></span>    
    Sales.SalesOrderDetail <span style="color: #0000FF"><strong>AS</strong></span> SOD
  INNER JOIN Production.Product <span style="color: #0000FF"><strong>AS</strong></span> P <span style="color: #0000FF"><strong>ON</strong></span> SOD.ProductID <span style="color: #4444FF">=</span> P.ProductID

<span style="color: #0000FF"><strong>WHERE</strong></span>   SOD.SalesOrderID <span style="color: #0000FF"><strong>IN</strong></span> 
<span style="color: #4444FF">(</span>
    <span style="color: #008000">/* This inner query should retrieve all orders that have contained the productID */</span>
    <span style="color: #0000FF"><strong>SELECT</strong></span> DISTINCT SalesOrderID 
    <span style="color: #0000FF"><strong>FROM</strong></span> Sales.SalesOrderDetail
    <span style="color: #0000FF"><strong>WHERE</strong></span> ProductID <span style="color: #4444FF">=</span> @ProductID
<span style="color: #4444FF">)</span>
<span style="color: #0000FF"><strong>AND</strong></span> SOD.ProductID <span style="color: #4444FF">!</span><span style="color: #4444FF">=</span> @ProductID 

<span style="color: #0000FF"><strong>GROUP BY</strong></span> SOD.ProductID<span style="color: #4444FF">,</span> P.[<span style="color: #0000FF"><strong>Name</strong></span>] 

<span style="color: #0000FF"><strong>ORDER BY</strong></span> TotalNum <span style="color: #0000FF"><strong>DESC</strong></span>;


</pre>

        <b>Database Tables Used: </b>

        <P><i>Product:</i> &nbsp;The Product table contains the core information about all of the items for sale
        on the Adventure Works Cycles web site. Its primary key is the ProductID identity field. 

        <p>

        <img src="1x1.gif" width=25> <IMG src="../docs/images/product.gif" width="386" height="501">

        <p><i>SalesOrderDetail:</i>
        &nbsp;The SalesOrderDetail table contains detailed product information for each
        order in the system. For each product in an order, an entry is made into the
        SalesOrderDetail table containing the ProductID, Quantity and current UnitCost of
        the item. There is a many to one relationship between SalesOrderDetail table and
        the SalesOrderHeader table. Note the primary key in the table is both SalesOrderID and
        LineNumber (since those two columns are the minimum required to guarantee
        uniqueness of a record).
        <p>
            <IMG src="1x1.gif" width="25"> <IMG src="../docs/images/SalesOrderHeader-Detail.gif" width="585" height="455">
 
    </body>
</html>
